package private

import (
	"dc-go/global"
	"dc-go/model"
	"errors"
	"fmt"
	"strconv"
	"strings"

	"gorm.io/gorm"
)

type ServiceSqlparam struct{}

func (s *ServiceSqlparam) GetSqlparamList(RequestGetSqlparamList model.RequestGetSqlparamList) (sqlparam interface{}, total int64, err error) {
	if RequestGetSqlparamList.SortBy == "" {
		RequestGetSqlparamList.SortBy = "id"
		RequestGetSqlparamList.Desc = false
	}
	pageSize := RequestGetSqlparamList.PageSize
	offset := RequestGetSqlparamList.PageSize * (RequestGetSqlparamList.Page - 1)
	db := global.DcDb.Model(&model.SysSqlparam{})
	var sqlparamList []model.SysSqlparam
	//配置搜索
	if RequestGetSqlparamList.SqlparamCode != "" {
		db = db.Where("sqlparam_code like ?", "%"+RequestGetSqlparamList.SqlparamCode+"%")
	}

	if RequestGetSqlparamList.SqlparamName != "" {
		db = db.Where("sqlparam_name like ?", "%"+RequestGetSqlparamList.SqlparamName+"%")
	}

	if RequestGetSqlparamList.SqlparamType != "" {
		db = db.Where("sqlparam_type like ?", "%"+RequestGetSqlparamList.SqlparamType+"%")
	}

	if RequestGetSqlparamList.CreatedBy != "" {
		db = db.Where("created_by like ?", "%"+RequestGetSqlparamList.CreatedBy+"%")
	}

	if RequestGetSqlparamList.UpdatedBy != "" {
		db = db.Where("updated_by like ?", "%"+RequestGetSqlparamList.UpdatedBy+"%")
	}

	err = db.Count(&total).Error
	if err != nil {
		return
	}
	err = db.Limit(pageSize).Offset(offset).Order(model.OrderByColumn(RequestGetSqlparamList.SortBy, RequestGetSqlparamList.Desc)).Find(&sqlparamList).Error
	return sqlparamList, total, err
}

func (s *ServiceSqlparam) EditSqlparam(toEditSqlparam model.RequestEditSqlparam, empeeAcct string) (err error) {
	var sysSqlparam model.SysSqlparam
	if err = global.DcDb.Where("id = ?", toEditSqlparam.Id).First(&sysSqlparam).Error; err != nil {
		return err
	}
	if *sysSqlparam.Source == "internal" {
		return errors.New("系统内置不允许编辑：" + toEditSqlparam.SqlparamCode)
	}
	sysSqlparam.UpdatedBy = empeeAcct
	sysSqlparam.SqlparamName = toEditSqlparam.SqlparamName
	sysSqlparam.SqlparamType = toEditSqlparam.SqlparamType
	sysSqlparam.DefaultValue = toEditSqlparam.DefaultValue
	memo := toEditSqlparam.Memo
	if memo == "" {
		sysSqlparam.Memo = nil
	} else {
		sysSqlparam.Memo = &memo
	}
	if toEditSqlparam.SqlparamType == "date" {
		sysSqlparam.OptionSql = nil
		sysSqlparam.UseVariable = toEditSqlparam.UseVariable
	}
	if toEditSqlparam.SqlparamType == "optionsFromQuery" {
		OptionSql := toEditSqlparam.OptionSql
		sysSqlparam.UseVariable = ""
		sysSqlparam.OptionSql = &OptionSql
	}
	if toEditSqlparam.SqlparamType == "optionsFromPreDefined" && len(toEditSqlparam.Options) > 0 {
		var i uint = 1
		options := strings.Split(toEditSqlparam.Options, ",")
		var sqlparamOptions []model.SysSqlparamOption
		for _, option := range options {
			sqlparamOption := &model.SysSqlparamOption{
				DcModel: model.DcModel{
					CreatedBy: empeeAcct,
					UpdatedBy: empeeAcct,
					Status:    toEditSqlparam.Status,
					Sort:      i,
				},
				SqlparamCode: toEditSqlparam.SqlparamCode,
				Option:       option,
			}
			sqlparamOptions = append(sqlparamOptions, *sqlparamOption)
		}
		sysSqlparam.UseVariable = ""
		sysSqlparam.OptionSql = nil
		var sysSqlparamOption model.SysSqlparamOption
		err = global.DcDb.Where("sqlparam_code = ?", sysSqlparam.SqlparamCode).Delete(&sysSqlparamOption).Error
		if err != nil {
			return err
		}
		global.DcDb.Model(&sysSqlparam).Association("Options").Append(sqlparamOptions)
	}

	err = global.DcDb.Save(&sysSqlparam).Error
	return err
}

func (s *ServiceSqlparam) AddSqlparam(toAddSqlparam model.RequestAddSqlparam, empeeAcct string) (err error) {
	var sysSqlparam model.SysSqlparam
	if !errors.Is(global.DcDb.Where("sqlparam_code = ?", toAddSqlparam.SqlparamCode).First(&sysSqlparam).Error, gorm.ErrRecordNotFound) {
		return errors.New("此SQL参数编码已存在: " + toAddSqlparam.SqlparamCode)
	}
	sysSqlparam.CreatedBy = empeeAcct
	sysSqlparam.UpdatedBy = empeeAcct
	sysSqlparam.SqlparamCode = toAddSqlparam.SqlparamCode
	sysSqlparam.SqlparamName = toAddSqlparam.SqlparamName
	sysSqlparam.SqlparamType = toAddSqlparam.SqlparamType
	sysSqlparam.DefaultValue = toAddSqlparam.DefaultValue
	memo := toAddSqlparam.Memo
	if memo == "" {
		sysSqlparam.Memo = nil
	} else {
		sysSqlparam.Memo = &memo
	}
	if toAddSqlparam.SqlparamType == "date" {
		sysSqlparam.OptionSql = nil
		sysSqlparam.UseVariable = toAddSqlparam.UseVariable
	}
	if toAddSqlparam.SqlparamType == "optionsFromQuery" {
		OptionSql := toAddSqlparam.OptionSql
		sysSqlparam.UseVariable = ""
		sysSqlparam.OptionSql = &OptionSql
	}
	if toAddSqlparam.SqlparamType == "optionsFromPreDefined" && len(toAddSqlparam.Options) > 0 {
		var i uint = 1
		options := strings.Split(toAddSqlparam.Options, ",")
		var sqlparamOptions []model.SysSqlparamOption
		for _, option := range options {
			sqlparamOption := &model.SysSqlparamOption{
				DcModel: model.DcModel{
					CreatedBy: empeeAcct,
					UpdatedBy: empeeAcct,
					Status:    toAddSqlparam.Status,
					Sort:      i,
				},
				SqlparamCode: toAddSqlparam.SqlparamCode,
				Option:       option,
			}
			sqlparamOptions = append(sqlparamOptions, *sqlparamOption)
		}
		sysSqlparam.UseVariable = ""
		sysSqlparam.OptionSql = nil
		var sysSqlparamOption model.SysSqlparamOption
		err = global.DcDb.Where("sqlparam_code = ?", sysSqlparam.SqlparamCode).Delete(&sysSqlparamOption).Error
		if err != nil {
			return err
		}
		global.DcDb.Model(&sysSqlparam).Association("Options").Append(sqlparamOptions)
	}
	err = global.DcDb.Create(&sysSqlparam).Error
	return err
}

func (s *ServiceSqlparam) QuerySqlparamById(id uint) (SqlParamInfo *model.ResponseQuerySqlparam, err error) {
	var sqlparam model.SysSqlparam
	err = global.DcDb.First(&sqlparam, "id = ?", id).Error
	if err != nil {
		return nil, err
	}
	SqlParamInfo = &model.ResponseQuerySqlparam{
		Id:           sqlparam.Id,
		Sort:         sqlparam.Sort,
		Status:       sqlparam.Status,
		SqlparamCode: sqlparam.SqlparamCode,
		SqlparamName: sqlparam.SqlparamName,
		SqlparamType: sqlparam.SqlparamType,
		DefaultValue: sqlparam.DefaultValue,
	}
	if sqlparam.Memo != nil {
		memo := *(sqlparam.Memo)
		SqlParamInfo.Memo = memo
	}
	var optionSql string
	if sqlparam.SqlparamType == "optionsFromQuery" {
		if sqlparam.OptionSql != nil {
			optionSql = *(sqlparam.OptionSql)
			SqlParamInfo.OptionSql = optionSql
		}
	}
	if sqlparam.SqlparamType == "optionsFromPreDefined" {
		var sqlparamOptionList []model.SysSqlparamOption
		err = global.DcDb.Where("sqlparam_code = ?", sqlparam.SqlparamCode).Find(&sqlparamOptionList).Error
		if err != nil {
			return nil, err
		}
		SqlParamInfo.Options = ""
		for _, sqlparamOption := range sqlparamOptionList {
			SqlParamInfo.Options += sqlparamOption.Option + ","
		}
		SqlParamInfo.Options = strings.TrimRight(SqlParamInfo.Options, ",")
	}
	if sqlparam.SqlparamType == "date" {
		SqlParamInfo.UseVariable = sqlparam.UseVariable
	}
	return SqlParamInfo, err
}
func (s *ServiceSqlparam) QuerySqlparamBySqlparamCode(sqlparamCode string) (SqlParamInfo model.SysSqlparam, err error) {
	var sqlparam model.SysSqlparam
	err = global.DcDb.First(&sqlparam, "sqlparam_code = ?", sqlparamCode).Error
	return sqlparam, err
}
func (s *ServiceSqlparam) QuerySqlparamOptionsBySqlparamCode(sqlparamCode string, constraint string) (resOptions []model.OrgOption, err error) {
	var sqlparam model.SysSqlparam
	var options []string
	err = global.DcDb.First(&sqlparam, "sqlparam_code = ?", sqlparamCode).Error
	if err != nil {
		return nil, err
	}
	if sqlparam.SqlparamType == "optionsFromPreDefined" {
		err = global.DcDb.Model(&model.SysSqlparamOption{}).Where("sqlparam_code = ?", sqlparamCode).Pluck("option", &options).Error
		if err != nil {
			return nil, err
		}
		for _, option := range options {
			resOption := model.OrgOption{
				Option: option,
			}
			resOptions = append(resOptions, resOption)
		}
	} else if sqlparam.SqlparamType == "optionsFromQuery" {

		err = global.DcDb.Raw(*sqlparam.OptionSql).First(&options).Error
		if err != nil {
			return nil, err
		}
		for _, option := range options {
			resOption := model.OrgOption{
				Option: option,
			}
			resOptions = append(resOptions, resOption)
		}
	} else if sqlparam.SqlparamType == "internal" {
		var result []model.OrgOption
		// if sqlparam.SqlparamCode == "mkt_area" {
		// 	var queryMktAreaSql = "select distinct null constraint,mkt_area_name \"option\" from hx.tb_b_dim_region order by mkt_area_name"
		// 	err = global.DcDb.Raw(queryMktAreaSql).Scan(&result).Error
		// 	if err != nil {
		// 		return nil, err
		// 	}
		// 	return result, nil
		// } else if sqlparam.SqlparamCode == "dept" {
		// 	var queryDeptSql = "select distinct mkt_area_name constraint,dept_name \"option\" from hx.tb_b_dim_region order by mkt_area_name,dept_name"
		// 	err = global.DcDb.Raw(queryDeptSql).Scan(&result).Error
		// 	if err != nil {
		// 		return nil, err
		// 	}
		// 	return result, nil
		// } else if sqlparam.SqlparamCode == "duty_zone" {
		// 	var queryDutyZoneSql = "select distinct mkt_area_name||dept_name constraint,duty_zone_name \"option\" from hx.tb_b_dim_region order by mkt_area_name||dept_name,duty_zone_name"
		// 	err = global.DcDb.Raw(queryDutyZoneSql).Scan(&result).Error
		// 	if err != nil {
		// 		return nil, err
		// 	}
		// 	return result, nil
		// } else if sqlparam.SqlparamCode == "region" {
		// 	var queryRegionSql = "select distinct mkt_area_name||dept_name||duty_zone_name constraint,region_name \"option\" from hx.tb_b_dim_region order by mkt_area_name||dept_name||duty_zone_name,region_name"
		// 	err = global.DcDb.Raw(queryRegionSql).Scan(&result).Error
		// 	if err != nil {
		// 		return nil, err
		// 	}
		// 	return result, nil
		// }
		param := model.OrgOption{
			Constraint: constraint,
			Level:      sqlparam.SqlparamCode,
		}
		fmt.Printf("%+v", param)
		err = global.DcDb.Table("datacenter.orgoption2023").Where(&param).Scan(&result).Error
		if err != nil {
			return nil, err
		}
		return result, nil
	}

	return resOptions, nil
}

func (s *ServiceSqlparam) DeleteSqlparamById(id uint) (err error) {
	var sysSqlparam model.SysSqlparam
	if err = global.DcDb.Where("id = ?", id).First(&sysSqlparam).Error; err != nil {
		return err
	}
	if *sysSqlparam.Source == "internal" {
		return errors.New("系统内置不允许删除：" + sysSqlparam.SqlparamCode)
	}
	// 如果还有关联，提示关联的SQL
	var associatedSql []model.SysSql
	global.DcDb.Model(&sysSqlparam).Association("Sql").Find(&associatedSql)
	if len(associatedSql) > 0 {
		return errors.New("当前还有" + associatedSql[0].SqlCode + "等" + strconv.Itoa(len(associatedSql)) + "条Sql关联了这个参数。请先删除关联的SQL")
	}
	// 删除 sys_sql 表的数据
	var sysSqlparamOption model.SysSqlparamOption
	err = global.DcDb.Where("sqlparam_code = ?", sysSqlparam.SqlparamCode).Delete(&sysSqlparamOption).Error
	if err != nil {
		return err
	}
	err = global.DcDb.Unscoped().Delete(&sysSqlparam).Error
	if err != nil {
		return err
	}
	return err
}

func (s *ServiceSqlparam) ValidateVariable(variable string) (res string, err error) {
	sqlString := "select (" + variable + ")::text"
	err = global.DcDb.Raw(sqlString).Find(&res).Error
	return res, err
}

func (s *ServiceSqlparam) ValidateSqlparamOptionSql(optionSql string) (res string, err error) {
	selectIndex := strings.Index(strings.ToLower(optionSql), "select")
	fromIndex := strings.Index(strings.ToLower(optionSql), "from")
	if selectIndex == -1 || fromIndex == -1 || selectIndex > fromIndex {
		return "", errors.New("请检查查询语句是否正确")
	}
	str := string([]rune(optionSql)[selectIndex+6 : fromIndex])
	if strings.Contains(str, ",") {
		return "", errors.New("查询语句只能返回一个字段")
	}
	err = global.DcDb.Raw(optionSql).First(&res).Error
	if err != nil {
		return "", err
	}
	return res, err
}
